package cn.xtits.xtf.common.db;

/**
 * Created by Administrator on 2016/3/5.
 */

import java.sql.*;
import java.util.*;
import java.util.Date;

/**
 * 数据库连接类
 */
public class DbConn implements AutoCloseable {
    /**
     * 数据库连接内部字段
     */
    private Connection _conn;

    /**
     * 连接字符串
     */
    public String ConnString;

    /**
     * 创建连接
     *
     * @param conn 已有的连接
     * @return
     */
    public DbConn(Connection conn) {
        _conn = conn;
    }

    /**
     * 创建连接
     *
     * @param connectstring 已有的连接
     * @return
     */
    public static DbConn createConn(String connectstring) throws Exception {
        DbConn cn = new DbConn(DruidHelper.GetConnection(connectstring));
        return cn;
    }

    /**
     * 打开数据库连接

     public final void open() {
     //string Err = "";
     //if (!Lib.Sys.GetRockState(0,ref Err))
     //    throw new Exception(Err);
     //_conn();
     }
     */

    /**
     * 关闭数据库连接
     */
    public final void close() throws Exception {
        _conn.close();
    }

    /**
     * 释放
     */
    public final void dispose() throws Exception {
        _conn.close();
        _conn = null;
    }

    /**
     * 取得数据库连接对象
     *
     * @return
     */
    public final Connection getConnection() {
        return _conn;
    }

    private List<Map<String, Object>> convert(ResultSet rs) throws SQLException {
        List<Map<String, Object>> list = new ArrayList<>();
        if (rs != null && !rs.isClosed()) {
            ResultSetMetaData meta = rs.getMetaData();
            int colCount = meta.getColumnCount();
            while (rs.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 1; i <= colCount; i++) {
                    String key = meta.getColumnLabel(i);
                    Object value = rs.getObject(i);
                    map.put(key, value);
                }
                list.add(map);

            }
        }
        return list;
    }

    private void attachParameters(PreparedStatement statement, Object... values) throws Exception {
        if (values != null) {
            for (int i = 0; i < values.length; i++) {
                statement.setObject(i + 1, values[i]);
            }
        }
    }

    public int executeNonQuery(String sql, Object... parameterValues) throws SQLException, Exception {

        Connection connection = null;
        PreparedStatement statement = null;
        int result = 0;
        connection = getConnection();
        statement = connection.prepareStatement(sql);
        attachParameters(statement, parameterValues);
        result = statement.executeUpdate();
        statement.clearParameters();

        return result;
    }

    public int executeBatch(List<String> sql) throws SQLException, Exception {

        Connection connection = null;
        Statement statement = null;
        int result = 0;
        connection = getConnection();
        statement = connection.createStatement();
        for (String s : sql) {
            statement.addBatch(s);
        }
        int[] ints = statement.executeBatch();
        for (int anInt : ints) {
            result += anInt;
        }
        return result;
    }

    public Object executeScalar(String sql, Object... parameterValues) throws Exception {
        ResultSet rs = null;
        Object value = null;
        Connection connection = null;

        connection = getConnection();
        rs = executeResultSet(sql, parameterValues);
        if (rs != null) {
            rs.next();
            value = rs.getObject(1);
        }


        return value;
    }

    private ResultSet executeResultSet(String sql, Object... parameterValues) throws Exception {
        PreparedStatement statement = null;
        ResultSet rs = null;


        statement = getConnection().prepareStatement(sql);
        attachParameters(statement, parameterValues);
        rs = statement.executeQuery();
        statement.clearParameters();

        return rs;
    }

    public List<Map<String, Object>> executeList(String sql, Object... parameterValues) throws Exception {
        ResultSet rs = null;
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        Connection connection = null;

        connection = getConnection();
        rs = executeResultSet(sql, parameterValues);
        list = convert(rs);

        return list;
    }

    public Date GetServerDate() throws Exception {
        Object date = executeScalar("select GetDate() as aDate");
        if (date != null)
            return ConvertHelper.objToDateTime(date);
        throw new Exception("获取数据库服务器时间出错");
    }

    public boolean TableIsExist(String aTableName) throws Exception {
        List<Map<String, Object>> ds = executeList("Select name from sysobjects where Name='" + aTableName + "'");
        if (ds == null || ds.size() == 0)
            return false;
        else
            return true;
    }
}
